﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;

namespace BulkPlaceExpressTestOrders
{
    class Customer
    {
        public int Id { get; set; }

        public string Username { get; set; }

        public string Email { get; set; }

        public string TimeZoneId { get; set; }

        public int BillingAddress_Id { get; set; }

        public int ShippingAddress_Id { get; set; }
    }

    class Address
    {
        public int Id { get; set; }
        public string FirstName { get; set; }

        public string LastName { get; set; }

        public string Email { get; set; }

        public string Company { get; set; }

        public int? CountryId { get; set; }

        public int? StateProvinceId { get; set; }

        public string City { get; set; }

        public string Address1 { get; set; }

        public string Address2 { get; set; }

        public string ZipPostalCode { get; set; }

        public string PhoneNumber { get; set; }

        public string CellPhoneNumber { get; set; }

        public int? TypeId { get; set; }

        public AddressType? Type { get; set; }
    }

    class Order
    {
        #region Properties

        public int Id { get; set; }
        /// <summary>
        /// Gets or sets the order identifier
        /// </summary>
        public Guid OrderGuid { get; set; }

        /// <summary>
        /// Gets or sets the group identifier 
        /// </summary>
        public Guid? OrderGroupGuid { get; set; }

        /// <summary>
        /// Gets or sets the store identifier which the order belongs
        /// </summary>
        public int? StoreId { get; set; }

        /// <summary>
        /// Gets or sets the district identifier
        /// </summary>
        public int DistrictId { get; set; }

        /// <summary>
        /// Gets or sets the customer identifier
        /// </summary>
        public int CustomerId { get; set; }

        /// <summary>
        /// Gets or sets the billing address identifier
        /// </summary>
        public int BillingAddressId { get; set; }

        /// <summary>
        /// Gets or sets the shipping address identifier
        /// </summary>
        public int? ShippingAddressId { get; set; }

        /// <summary>
        /// Gets or sets an order status identifier
        /// </summary>
        public int OrderStatusId { get; set; }

        /// <summary>
        /// Gets or sets the shipping status identifier
        /// </summary>
        public int ShippingStatusId { get; set; }

        /// <summary>
        /// Gets or sets the payment status identifier
        /// </summary>
        public int PaymentStatusId { get; set; }

        /// <summary>
        /// Gets or sets the payment method system name
        /// </summary>
        public string PaymentMethodSystemName { get; set; }

        /// <summary>
        /// Gets or sets the customer currency code (at the moment of order placing)
        /// </summary>
        public string CustomerCurrencyCode { get; set; }

        /// <summary>
        /// Gets or sets the currency rate
        /// </summary>
        public decimal CurrencyRate { get; set; }

        /// <summary>
        /// Gets or sets the customer tax display type identifier
        /// </summary>
        public int CustomerTaxDisplayTypeId { get; set; }

        /// <summary>
        /// Gets or sets the VAT number (the European Union Value Added Tax)
        /// </summary>
        public string VatNumber { get; set; }

        /// <summary>
        /// Gets or sets the order subtotal (incl tax)
        /// </summary>
        public decimal OrderSubtotalInclTax { get; set; }

        /// <summary>
        /// Gets or sets the order subtotal (excl tax)
        /// </summary>
        public decimal OrderSubtotalExclTax { get; set; }

        /// <summary>
        /// Gets or sets the order subtotal discount (incl tax)
        /// </summary>
        public decimal OrderSubTotalDiscountInclTax { get; set; }

        /// <summary>
        /// Gets or sets the order subtotal discount (excl tax)
        /// </summary>
        public decimal OrderSubTotalDiscountExclTax { get; set; }

        /// <summary>
        /// Gets or sets the order shipping (incl tax)
        /// </summary>
        public decimal OrderShippingInclTax { get; set; }

        /// <summary>
        /// Gets or sets the order shipping (excl tax)
        /// </summary>
        public decimal OrderShippingExclTax { get; set; }

        /// <summary>
        /// Gets or sets the payment method additional fee (incl tax)
        /// </summary>
        public decimal PaymentMethodAdditionalFeeInclTax { get; set; }

        /// <summary>
        /// Gets or sets the payment method additional fee (excl tax)
        /// </summary>
        public decimal PaymentMethodAdditionalFeeExclTax { get; set; }

        /// <summary>
        /// Gets or sets the tax rates
        /// </summary>
        public string TaxRates { get; set; }

        /// <summary>
        /// Gets or sets the order tax
        /// </summary>
        public decimal OrderTax { get; set; }

        /// <summary>
        /// Gets or sets the order discount (applied to order total)
        /// </summary>
        public decimal OrderDiscount { get; set; }

        /// <summary>
        /// Gets or sets the order total
        /// </summary>
        public decimal OrderTotal { get; set; }

        /// <summary>
        /// Gets or sets the refunded amount
        /// </summary>
        public decimal RefundedAmount { get; set; }

        /// <summary>
        /// Gets or sets the value indicating whether reward points were earned for this order
        /// </summary>
        public bool RewardPointsWereAdded { get; set; }

        /// <summary>
        /// Gets or sets the checkout attribute description
        /// </summary>
        public string CheckoutAttributeDescription { get; set; }

        /// <summary>
        /// Gets or sets the checkout attributes in XML format
        /// </summary>
        public string CheckoutAttributesXml { get; set; }

        /// <summary>
        /// Gets or sets the customer language identifier
        /// </summary>
        public int CustomerLanguageId { get; set; }

        /// <summary>
        /// Gets or sets the affiliate identifier
        /// </summary>
        public int? AffiliateId { get; set; }

        /// <summary>
        /// Gets or sets the customer IP address
        /// </summary>
        public string CustomerIp { get; set; }

        /// <summary>
        /// Gets or sets a value indicating whether storing of credit card number is allowed
        /// </summary>
        public bool AllowStoringCreditCardNumber { get; set; }

        /// <summary>
        /// Gets or sets the card type
        /// </summary>
        public string CardType { get; set; }

        /// <summary>
        /// Gets or sets the card name
        /// </summary>
        public string CardName { get; set; }

        /// <summary>
        /// Gets or sets the card number
        /// </summary>
        public string CardNumber { get; set; }

        /// <summary>
        /// Gets or sets the masked credit card number
        /// </summary>
        public string MaskedCreditCardNumber { get; set; }

        /// <summary>
        /// Gets or sets the card CVV2
        /// </summary>
        public string CardCvv2 { get; set; }

        /// <summary>
        /// Gets or sets the card expiration month
        /// </summary>
        public string CardExpirationMonth { get; set; }

        /// <summary>
        /// Gets or sets the card expiration year
        /// </summary>
        public string CardExpirationYear { get; set; }

        /// <summary>
        /// Gets or sets the authorization transaction identifier
        /// </summary>
        public string AuthorizationTransactionId { get; set; }

        /// <summary>
        /// Gets or sets the authorization transaction code
        /// </summary>
        public string AuthorizationTransactionCode { get; set; }

        /// <summary>
        /// Gets or sets the authorization transaction result
        /// </summary>
        public string AuthorizationTransactionResult { get; set; }

        /// <summary>
        /// Gets or sets the capture transaction identifier
        /// </summary>
        public string CaptureTransactionId { get; set; }

        /// <summary>
        /// Gets or sets the capture transaction result
        /// </summary>
        public string CaptureTransactionResult { get; set; }

        /// <summary>
        /// Gets or sets the subscription transaction identifier
        /// </summary>
        public string SubscriptionTransactionId { get; set; }

        /// <summary>
        /// Gets or sets the purchase order number
        /// </summary>
        public string PurchaseOrderNumber { get; set; }

        /// <summary>
        /// Gets or sets the paid date and time
        /// </summary>
        public DateTime? PaidDateUtc { get; set; }

        /// <summary>
        /// Gets or sets the shipping method
        /// </summary>
        public string ShippingMethod { get; set; }

        /// <summary>
        /// Gets or sets the shipping rate computation method identifier
        /// </summary>
        public string ShippingRateComputationMethodSystemName { get; set; }

        /// <summary>
        /// Gets or sets a value indicating whether the entity has been deleted
        /// </summary>
        public bool Deleted { get; set; }

        /// <summary>
        /// Gets or sets the date and time of order creation
        /// </summary>
        public DateTime CreatedOnUtc { get; set; }

        /// <summary>
        /// Gets or sets the related invoice identifier
        /// </summary>
        public int? InvoiceId { get; set; }

        /// <summary>
        /// Gets or sets whether this is a pure logistics order
        /// </summary>
        public int OrderKindId { get; set; }

        /// <summary>
        /// Gets or sets the bound order identifier if this is a logistics order.
        /// </summary>
        public int? BoundOrderId { get; set; }

        /// <summary>
        /// Gets or sets the customized order by information
        /// </summary>
        public string OrderByInfo { get; set; }

        #endregion
    }

    #region enum
    enum AddressType : int
    {
        Unknown = 0,
        Business = 1,
        Residential = 2
    }
    enum AddtionalAttributes : int
    {
        None = 0,
        VerticalHandle = 1,
        Fragile = 2,
        DangerousGood = 4,
        NoLabel = 8,
        Insurance = 16,
        Free = 32,
        NoSignature = 64,
        SunDeliver = 128,
        SatDeliver = 256,
        NightDeliver = 512,
        InterDistrict = 1024,
        Residential = 2048,
        StoreOrderLowCharge = 4096
    }

    enum TaxDisplayType
    {
        /// <summary>
        /// Including tax
        /// </summary>
        IncludingTax = 0,
        /// <summary>
        /// Excluding tax
        /// </summary>
        ExcludingTax = 10,
    }
    enum OrderStatus : int
    {
        /// <summary>
        /// Pending
        /// </summary>
        Pending = 10,
        /// <summary>
        /// Processing
        /// </summary>
        Processing = 20,
        /// <summary>
        /// Complete
        /// </summary>
        Complete = 30,
        /// <summary>
        /// Cancelled
        /// </summary>
        Cancelled = 40
    }

    enum PaymentStatus : int
    {
        /// <summary>
        /// Pending
        /// </summary>
        Pending = 10,
        /// <summary>
        /// Authorized
        /// </summary>
        Authorized = 20,
        /// <summary>
        /// Paid
        /// </summary>
        Paid = 30,
        /// <summary>
        /// Partially Refunded
        /// </summary>
        PartiallyRefunded = 35,
        /// <summary>
        /// Refunded
        /// </summary>
        Refunded = 40,
        /// <summary>
        /// Voided
        /// </summary>
        Voided = 50,
    }

    enum ShippingStatus : int
    {
        /// <summary>
        /// Shipping not required
        /// </summary>
        ShippingNotRequired = 10,
        /// <summary>
        /// Not yet ready
        /// </summary>
        NotYetReady = 15,
        /// <summary>
        /// Not yet shipped
        /// </summary>
        NotYetShipped = 20,
        /// <summary>
        /// Partially shipped
        /// </summary>
        PartiallyShipped = 25,
        /// <summary>
        /// Shipped
        /// </summary>
        Shipped = 30,
        /// <summary>
        /// Delivered
        /// </summary>
        Delivered = 40,
        /// <summary>
        /// ReadyForPickUp
        /// </summary>
        ReadyForPickUp = 50,
        /// <summary>
        /// Delivering
        /// </summary>
        Delivering = 60,
        /// <summary>
        /// ReturningWarehouse
        /// </summary>
        ReturningWarehouse = 70,
        /// <summary>
        /// ReturningWarehouseFailedDelivery
        /// </summary>
        ReturningWarehouseFailedDelivery = 71,
        /// <summary>
        /// InWarehouse
        /// </summary>
        InWarehouse = 80,
        /// <summary>
        /// InWarehouseFailedDelivery
        /// </summary>
        InWarehouseFailedDelivery = 81,
        /// <summary>
        /// FailedPickUp
        /// </summary>
        FailedPickUp = 90,

        /// <summary>
        /// Called delivering
        /// </summary>
        Cancelled = 100,
    }
    enum OrderKind : int
    {
        Express = 10,
        Commerce = 20,
        ExpressMembership = 30
    }

    enum LocationStatus : byte
    {
        Unknown,
        ReadyForPickUp,
        PendingDropOff,
        ReadyForDropOff,
        Complete
    }

    #endregion

    class Surcharge
    {
        public int Id { get; set; }

        public int SurchargeTypeId { get; set; }

        public decimal PercentageOrFee { get; set; }

        public int? CountryId { get; set; }

        public int? StateProvinceId { get; set; }

        public string Zip { get; set; }
         
        public int OrderId { get; set; }

        public decimal Value { get; set; }

        public int OrderProductVariantId { get; set; }

        public virtual Order Order { get; set; }
    }

    class OrderProductVariant
    {
        public int Id { get; set; }
        /// <summary>
        /// Gets or sets the order product variant identifier
        /// </summary>
        public virtual Guid OrderProductVariantGuid { get; set; }

        /// <summary>
        /// Gets or sets the order identifier
        /// </summary>
        public virtual int OrderId { get; set; }

        /// <summary>
        /// Gets or sets the product variant identifier
        /// </summary>
        public virtual int ProductVariantId { get; set; }

        /// <summary>
        /// Gets or sets the quantity
        /// </summary>
        public virtual int Quantity { get; set; }

        /// <summary>
        /// Gets or sets the unit price in primary store currency (incl tax)
        /// </summary>
        public virtual decimal UnitPriceInclTax { get; set; }

        /// <summary>
        /// Gets or sets the unit price in primary store currency (excl tax)
        /// </summary>
        public virtual decimal UnitPriceExclTax { get; set; }

        /// <summary>
        /// Gets or sets the price in primary store currency (incl tax)
        /// </summary>
        public virtual decimal PriceInclTax { get; set; }

        /// <summary>
        /// Gets or sets the price in primary store currency (excl tax)
        /// </summary>
        public virtual decimal PriceExclTax { get; set; }

        /// <summary>
        /// Gets or sets the discount amount (incl tax)
        /// </summary>
        public virtual decimal DiscountAmountInclTax { get; set; }

        /// <summary>
        /// Gets or sets the discount amount (excl tax)
        /// </summary>
        public virtual decimal DiscountAmountExclTax { get; set; }

        /// <summary>
        /// Gets or sets the attribute description
        /// </summary>
        public virtual string AttributeDescription { get; set; }

        /// <summary>
        /// Gets or sets the product variant attributes in XML format
        /// </summary>
        public virtual string AttributesXml { get; set; }

        /// <summary>
        /// Gets or sets the download count
        /// </summary>
        public virtual int DownloadCount { get; set; }

        /// <summary>
        /// Gets or sets a value indicating whether download is activated
        /// </summary>
        public virtual bool IsDownloadActivated { get; set; }

        /// <summary>
        /// Gets or sets a value indicating whether the product variant is partially refund
        /// </summary>
        public virtual bool? IsRefund { get; set; }

        /// <summary>
        /// Gets or sets a license download identifier (in case this is a downloadable product)
        /// </summary>
        public virtual int? LicenseDownloadId { get; set; }

        /// <summary>
        /// Gets or sets the total weight of one item
        /// It's nullable for compatibility with the previous version of nopCommerce where was no such property
        /// </summary>
        public virtual decimal? ItemWeight { get; set; }

        /// <summary>
        /// Gets the order
        /// </summary>
        public virtual Order Order { get; set; }
    }

    class Shipment
    {
        /// <summary>
        /// Gets or sets the order identifier
        /// </summary>
        public int OrderId { get; set; }

        /// <summary>
        /// Gets or sets the tracking number of this shipment
        /// </summary>
        public string TrackingNumber { get; set; }

        /// <summary>
        /// Gets or sets the total weight of this shipment
        /// It's nullable for compatibility with the previous version of nopCommerce where was no such property
        /// </summary>
        public decimal? TotalWeight { get; set; }

        /// <summary>
        /// Gets or sets the length of the shipment package.
        /// </summary>
        public decimal? Length { get; set; }

        /// <summary>
        /// Gets or sets the width of the shipment package.
        /// </summary>
        public decimal? Width { get; set; }

        /// <summary>
        /// Gets or sets the height of the shipment package.
        /// </summary>
        public decimal? Height { get; set; }

        /// <summary>
        /// Gets or set the package identifier which bound to the current shipment 
        /// </summary>
        public int? PackageId { get; set; }

        /// <summary>
        /// null or 0 means nothing. 1 means it is fragile
        /// TODO: Make this enum
        /// </summary>
        public int? Attributes { get; set; }

        /// <summary>
        /// Gets or sets the insurance value
        /// </summary>
        public decimal? InsuranceValue { get; set; }

        /// <summary>
        /// Comments of the shipment
        /// </summary>
        public string Comment { get; set; }

        /// <summary>
        /// Gets or sets the shipped date and time
        /// </summary>
        public DateTime? ShippedDateUtc { get; set; }

        /// <summary>
        /// Gets or sets the delivery date and time
        /// </summary>
        public DateTime? DeliveryDateUtc { get; set; }

        /// <summary>
        /// Gets or sets the entity creation date
        /// </summary>
        public DateTime CreatedOnUtc { get; set; }

        /// <summary>
        /// Gets the order
        /// </summary>
        public virtual Order Order { get; set; }
    }

    class ShipmentOrderProductVariant
    {
        /// <summary>
        /// Gets or sets the shipment identifier
        /// </summary>
        public virtual int ShipmentId { get; set; }

        /// <summary>
        /// Gets or sets the order product variant identifier
        /// </summary>
        public virtual int OrderProductVariantId { get; set; }

        /// <summary>
        /// Gets or sets the quantity
        /// </summary>
        public virtual int Quantity { get; set; }

        /// <summary>
        /// Gets the shipment
        /// </summary>
        public virtual Shipment Shipment { get; set; }
    }

    class TaxRate 
    {
        /// <summary>
        /// Gets or sets the tax category identifier
        /// </summary>
        public virtual int TaxCategoryId { get; set; }

        /// <summary>
        /// Gets or sets the country identifier
        /// </summary>
        public virtual int CountryId { get; set; }

        /// <summary>
        /// Gets or sets the state/province identifier
        /// </summary>
        public virtual int StateProvinceId { get; set; }

        /// <summary>
        /// Gets or sets the surcharge type identifier
        /// </summary>
        public virtual int SurchargeTypeId { get; set; }

        /// <summary>
        /// Gets or sets whether charge is fixed or by percentage
        /// </summary>
        public virtual bool IsFixedCharge { get; set; }

        /// <summary>
        /// Gets or sets the zip
        /// </summary>
        public virtual string Zip { get; set; }

        /// <summary>
        /// Gets or sets the percentage
        /// </summary>
        public virtual decimal PercentageOrFee { get; set; }

        //new added by Jackie, not in database
        public decimal Value { get; set; }
    }

    class Charge
    {
        public decimal SubTotalExclTax { get; set; }
        public decimal FuelSurchargeExclTax { get; set; }
        public decimal Tax { get; set; }
        public decimal Total { get { return SubTotalExclTax + FuelSurchargeExclTax + Tax; } }
    }

    class LogisticsLocation
    {
        public virtual int AddressId { get; set; }

        public DateTime CreatedOnUtc { get; set; }

        public int Status { get; set; }

        public virtual int? DistrictId { get; set; }
    }

    class LogisticsPackage
    {
        public int ShipmentId { get; set; }

        public int? WarehouseId { get; set; }

        public int? ToWarehouseId { get; set; }

        public int PickUpLocationId { get; set; }

        public int DeliveryLocationId { get; set; }

        public int? ApplicatorId { get; set; }

        public int PackageNoteVersion { get; set; }

        public DateTime CreatedOnUtc { get; set; }

        public DateTime? UpdatedOnUtc { get; set; }

        public DateTime? ReadyOnUtc { get; set; }
        public DateTime? PickupEndOnUtc { get; set; }

        public int Status { get; set; }

        public int? DeliveryAttemptCount { get; set; }
    }

    class Highlight : IDisposable
    {
        private ConsoleColor old;
        public Highlight(ConsoleColor clr)
        {
            old = Console.ForegroundColor;
            Console.ForegroundColor = clr;
        }

        public void Dispose()
        {
            Console.ForegroundColor = old;
        }
    }

    class Program
    {
        // Provide the following information
        //local database
        //private static string userName = "sitelogin";
        //private static string password;
        //private static string dataSource = @"SHSERVER01\SQLEXPRESS";
        //private static string databaseName = "SpotshubLocal2";
        //private static int taxCategoryId = 17;

        private static string userName = "sitelogin@pv6c15guth";
        private static string password;
        private static string dataSource = "tcp:pv6c15guth.database.windows.net,1433";
        private static string databaseName = "SpotshubStage";
        private static int customerId = 0;
        private static int orderNumber = 0;
        private static StreamWriter file = null;

        private static bool refresh = true;
        private static int taxCategoryId = 25;
        private static int languageId = 1;
        private static int pvId = 433;
        private static int stateProvinceId = 2;
        private static int districtId = 1;

        static void Main(string[] args)
        {
            file = new StreamWriter(String.Format("PlaceExpressTestOrders{0}.log", DateTime.Now.Ticks), true);

            Console.Write("---Please input the name of database you want to connect:");
            databaseName = Console.ReadLine();
            //Console.Write("Please input the dataSource you want to coneect:");
            //dataSource = Console.ReadLine();
            //Console.Write("Please input the userName you want to use:");
            //userName = Console.ReadLine();

            Console.Write("---Please input the password:");
            password = ReadPassword();

            try
            {
                // Create a connection string for the sample database
                SqlConnectionStringBuilder connStringBuilder;
                connStringBuilder = new SqlConnectionStringBuilder();
                connStringBuilder.DataSource = dataSource;
                connStringBuilder.InitialCatalog = databaseName;
                connStringBuilder.Encrypt = true;
                connStringBuilder.TrustServerCertificate = true;
                connStringBuilder.UserID = userName;
                connStringBuilder.Password = password;
                List<Address> userAddresses = new List<Address>();
                List<Address> addresses = new List<Address>();

                using (SqlConnection conn = new SqlConnection(connStringBuilder.ToString()))
                {
                    using (SqlCommand command = conn.CreateCommand())
                    {
                        while (true)
                        {
                            if (refresh)
                            {
                                Console.Write("---Please input the order number (<50) you want to create:");
                                while (!int.TryParse(Console.ReadLine(), out orderNumber)) ;

                                Console.Write("---Please input the customer Id:");
                                while (!int.TryParse(Console.ReadLine(), out customerId)) ;

                                conn.Open();
                                Log("---Connected to databse {0}.", databaseName);

                                userAddresses.Clear();
                                addresses.Clear();

                                bool pass = orderNumber > 0 || orderNumber > 50;
                                if (!pass)
                                {
                                    Log("---Order number should be more than 0 and less than 50. Exiting...");
                                    conn.Close();
                                    break;
                                }
                                Log("---Order number check passed.");

                                var number = orderNumber * 2;
                                string cmdText = String.Format("SELECT TOP {0} * FROM Address WHERE STATEPROVINCEID = {1} AND CellPhoneNumber IS NOT NULL AND TypeId IS NOT NULL ORDER BY NEWID()", number, stateProvinceId);
                                command.CommandText = cmdText;
                                command.ExecuteNonQuery();

                                using (SqlDataReader reader = command.ExecuteReader())
                                {
                                    // Loop over the results
                                    while (reader.Read())
                                    {
                                        var addressId = (int)reader["Id"];
                                        var countryId = (int)reader["CountryId"];
                                        //var email = reader["Email"] != null ? reader["Email"].ToString().Trim() : "";
                                        var firstName = reader["FirstName"].ToString().Trim();
                                        var lastName = reader["LastName"].ToString().Trim();
                                        var company = reader["Company"] != null ? reader["Company"].ToString().Trim() : "";
                                        //var address1 = reader["Address1"].ToString().Trim();
                                        //var address2 = reader["Address2"] != null ? reader["Address2"].ToString().Trim() : "";
                                        //var cellphone = reader["CellPhoneNumber"].ToString().Trim();
                                        //var phone = reader["PhoneNumber"] != null ? reader["PhoneNumber"].ToString().Trim() : "";
                                        var city = reader["City"].ToString().Trim();
                                        var zipPostalCode = reader["ZipPostalCode"].ToString().Trim();
                                        var addrTypeId = (int?)reader["TypeId"];

                                        var address = new Address
                                        {
                                            Id = addressId,
                                            FirstName = firstName,
                                            LastName = lastName,
                                            Company = company,
                                            //Address1 = address1,
                                            //Address2 = address2,
                                            //CellPhoneNumber = cellphone,
                                            //PhoneNumber = phone,
                                            City = city,
                                            CountryId = countryId,
                                            //Email = email,
                                            StateProvinceId = stateProvinceId,
                                            TypeId = addrTypeId,
                                            ZipPostalCode = zipPostalCode

                                        };
                                        if (userAddresses.Count < orderNumber)
                                        {
                                            userAddresses.Add(address);
                                        }
                                        else
                                        {
                                            addresses.Add(address);
                                        }
                                    }
                                }

                                refresh = false;

                                cmdText = String.Format("SELECT * FROM Customer WHERE Id = {0}", customerId);
                                command.CommandText = cmdText;
                                command.ExecuteNonQuery();

                                var customer = new Customer();
                                using (SqlDataReader reader = command.ExecuteReader())
                                {
                                    // Loop over the results
                                    while (reader.Read())
                                    {
                                        var billingAddressId = (int)reader["BillingAddress_Id"];
                                        var shippingAddressId = (int)reader["ShippingAddress_Id"];

                                        if (billingAddressId == 0 || shippingAddressId == 0)
                                            Log("---Please set up billing address or shipping address for your customer Id : {0}.", customerId);

                                        customer = new Customer
                                        {
                                            Id = customerId,
                                            BillingAddress_Id = billingAddressId,
                                            ShippingAddress_Id = shippingAddressId
                                        };
                                        break;
                                    }
                                }

                                if (customer.Id == 0)
                                    Log("---No customer found according to your customer Id : {0}.", customerId);

                                conn.Close();

                                //Tax rate
                                conn.Open();
                                if (taxCategoryId == 0)
                                    Log("---taxCategoryId must set.");

                                cmdText = String.Format("SELECT * FROM TaxRate WHERE TAXCATEGORYID = {0} AND StateProvinceId = {1}", taxCategoryId, stateProvinceId);
                                command.CommandText = cmdText;
                                command.ExecuteNonQuery();

                                var TaxRateList = new List<TaxRate>();
                                using (SqlDataReader reader = command.ExecuteReader())
                                {
                                    // Loop over the results
                                    while (reader.Read())
                                    {
                                        var countryId = (int)reader["CountryId"];
                                        var spvId = (int)reader["StateProvinceId"];
                                        var surchargeTypeId = (int)reader["SurchargeTypeId"];
                                        var zip = reader["Zip"] != null ? reader["Zip"].ToString().Trim() : "";
                                        var isFixedCharge = (bool)reader["IsFixedCharge"];
                                        var percentageOrFee = (decimal)reader["PercentageOrFee"];

                                        var taxRate = new TaxRate
                                        {
                                            CountryId = countryId,
                                            StateProvinceId = spvId,
                                            SurchargeTypeId = surchargeTypeId,
                                            TaxCategoryId = taxCategoryId,
                                            Zip = String.IsNullOrEmpty(zip) ? "''" : zip,
                                            IsFixedCharge = isFixedCharge,
                                            PercentageOrFee = percentageOrFee,
                                            Value = 0
                                        };
                                        TaxRateList.Add(taxRate);
                                    }
                                }
                                if (TaxRateList == null || TaxRateList.Count() == 0)
                                    Log("---No tax reate found with this tax category id");

                                conn.Close();

                                #region set oi, li and pi

                                decimal taxRates = TaxRateList.First().PercentageOrFee / 100M;
                                for (int i = 0; i < orderNumber; i++)
                                {
                                    conn.Open();
                                    // Start a local transaction.
                                    SqlTransaction sqlTran = conn.BeginTransaction();
                                    command.Transaction = sqlTran;

                                    try
                                    {
                                        var userAddress = userAddresses[i];
                                        var addr = addresses[i];
                                        var orderInfo = String.IsNullOrEmpty(userAddress.Company) ? userAddress.Company : String.Format("{0} {1}", userAddress.FirstName, userAddress.LastName);

                                        var charge = CalculateTotal(taxRates);

                                        #region insert order
                                        int orderId = -1;
                                        var order = new Order()
                                        {
                                            CustomerId = customer.Id,
                                            CustomerLanguageId = languageId,
                                            CustomerTaxDisplayTypeId = (int)TaxDisplayType.IncludingTax,
                                            CustomerIp = "'127.0.0.1'",
                                            OrderSubtotalInclTax = decimal.Zero,
                                            OrderSubtotalExclTax = charge.SubTotalExclTax,
                                            OrderSubTotalDiscountInclTax = decimal.Zero,
                                            OrderSubTotalDiscountExclTax = decimal.Zero,
                                            OrderShippingInclTax = decimal.Zero,
                                            OrderShippingExclTax = charge.FuelSurchargeExclTax,
                                            PaymentMethodAdditionalFeeInclTax = decimal.Zero,
                                            PaymentMethodAdditionalFeeExclTax = decimal.Zero,
                                            TaxRates = taxRates.ToString(),
                                            OrderTax = charge.Tax,
                                            OrderTotal = charge.Total,
                                            RefundedAmount = decimal.Zero,
                                            OrderDiscount = decimal.Zero,
                                            CustomerCurrencyCode = "'CAD'",
                                            CurrencyRate = 1M,
                                            OrderStatusId = (int)OrderStatus.Processing,
                                            //AllowStoringCreditCardNumber = false,
                                            PaymentStatusId = (int)PaymentStatus.Pending,
                                            BillingAddressId = customer.BillingAddress_Id,
                                            ShippingAddressId = customer.ShippingAddress_Id,
                                            ShippingStatusId = (int)ShippingStatus.ReadyForPickUp,
                                            ShippingMethod = "'SpotsHUB EXPRESS'",
                                            ShippingRateComputationMethodSystemName = "'Shipping.Spotshub'",
                                            //CreatedOnUtc = DateTime.UtcNow,
                                            OrderKindId = (int)OrderKind.Express,
                                            OrderByInfo = String.Format("'{0}'", orderInfo),
                                            DistrictId = districtId,
                                            CheckoutAttributeDescription = "''",
                                            CheckoutAttributesXml = "''",
                                            CardType = "''",
                                            CardName = "''",
                                            CardNumber = "''",
                                            MaskedCreditCardNumber = "''",
                                            CardCvv2 = "''",
                                            CardExpirationMonth = "''",
                                            CardExpirationYear = "''",
                                            PaymentMethodSystemName = "''",
                                            AuthorizationTransactionId = "''",
                                            AuthorizationTransactionCode = "''",
                                            AuthorizationTransactionResult = "''",
                                            CaptureTransactionId = "''",
                                            CaptureTransactionResult = "''",
                                            SubscriptionTransactionId = "''",
                                            PurchaseOrderNumber = "''",
                                            VatNumber = "''",
                                        };

                                        cmdText = String.Format("INSERT INTO [Order] (OrderGuid, CustomerId, CustomerLanguageId, CustomerTaxDisplayTypeId, CustomerIp, OrderSubtotalInclTax, OrderSubtotalExclTax, OrderSubTotalDiscountInclTax, OrderSubTotalDiscountExclTax, OrderShippingInclTax, OrderShippingExclTax, PaymentMethodAdditionalFeeInclTax, PaymentMethodAdditionalFeeExclTax, TaxRates, OrderTax, OrderTotal, RefundedAmount, OrderDiscount, CheckoutAttributeDescription, CheckoutAttributesXml, CustomerCurrencyCode, CurrencyRate, OrderStatusId, CardType, CardName, CardNumber, MaskedCreditCardNumber, CardCvv2, CardExpirationMonth, CardExpirationYear, PaymentMethodSystemName, AuthorizationTransactionId, AuthorizationTransactionCode, AuthorizationTransactionResult, CaptureTransactionId, CaptureTransactionResult, SubscriptionTransactionId, PurchaseOrderNumber, PaymentStatusId, BillingAddressId, ShippingAddressId, ShippingStatusId, ShippingMethod, ShippingRateComputationMethodSystemName, VatNumber, OrderKindId, OrderByInfo, DistrictId, AllowStoringCreditCardNumber, RewardPointsWereAdded, Deleted, CreatedOnUtc) VALUES (NEWID(), {0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18},{19},{20},{21},{22},{23},{24},{25},{26},{27},{28},{29},{30},{31},{32},{33},{34},{35},{36},{37},{38},{39},{40},{41},{42},{43},{44}, {45}, {46}, 0, 0, 0, CURRENT_TIMESTAMP);SELECT CAST(scope_identity() AS int)", 
                                                    order.CustomerId, order.CustomerLanguageId, order.CustomerTaxDisplayTypeId, order.CustomerIp, order.OrderSubtotalInclTax, 
                                                    order.OrderSubtotalExclTax, order.OrderSubTotalDiscountInclTax, order.OrderSubTotalDiscountExclTax,
                                                    order.OrderShippingInclTax, order.OrderShippingExclTax, order.PaymentMethodAdditionalFeeInclTax, order.PaymentMethodAdditionalFeeExclTax, order.TaxRates, order.OrderTax,
                                                    order.OrderTotal, order.RefundedAmount, order.OrderDiscount, order.CheckoutAttributeDescription, order.CheckoutAttributesXml, order.CustomerCurrencyCode, order.CurrencyRate,
                                                    order.OrderStatusId, order.CardType, order.CardName, order.CardNumber, order.MaskedCreditCardNumber, order.CardCvv2,
                                                    order.CardExpirationMonth, order.CardExpirationYear, order.PaymentMethodSystemName, order.AuthorizationTransactionId, order.AuthorizationTransactionCode, order.AuthorizationTransactionResult,
                                                    order.CaptureTransactionId, order.CaptureTransactionResult, order.SubscriptionTransactionId, order.PurchaseOrderNumber, order.PaymentStatusId,
                                                    order.BillingAddressId, order.ShippingAddressId, order.ShippingStatusId, order.ShippingMethod, order.ShippingRateComputationMethodSystemName, order.VatNumber,
                                                    order.OrderKindId, order.OrderByInfo, order.DistrictId);
                                        command.CommandText = cmdText;

                                        orderId = (int)command.ExecuteScalar();
                                        if (orderId < 0)
                                            Log(String.Format("---Failed creating order. For order No.{1}", i));
                                        #endregion

                                        #region Add orderproductvariant
                                        int opvId = -1;
                                        var opv = new OrderProductVariant()
                                        {
                                            OrderId = orderId,
                                            ProductVariantId = pvId,
                                            UnitPriceInclTax = decimal.Zero,
                                            UnitPriceExclTax = decimal.Zero,
                                            PriceInclTax = decimal.Zero,
                                            PriceExclTax = charge.SubTotalExclTax,
                                            AttributeDescription = "'SpotsHUB Letter Size Package'",
                                            Quantity = 1,
                                            DiscountAmountInclTax = decimal.Zero,
                                            DiscountAmountExclTax = decimal.Zero,
                                            DownloadCount = 0,
                                            LicenseDownloadId = 0,
                                            ItemWeight = decimal.Zero
                                        };

                                        command.CommandText =
                                                String.Format("INSERT INTO OrderProductVariant(OrderProductVariantGuid, OrderId, ProductVariantId, UnitPriceInclTax, UnitPriceExclTax, PriceInclTax, PriceExclTax, AttributeDescription, Quantity, DiscountAmountInclTax, DiscountAmountExclTax,DownloadCount,LicenseDownloadId,ItemWeight, IsDownloadActivated) VALUES (NEWID(), {0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12}, 0);SELECT CAST(scope_identity() AS int)",
                                                opv.OrderId, opv.ProductVariantId, opv.UnitPriceInclTax, opv.UnitPriceExclTax,
                                                opv.PriceInclTax, opv.PriceExclTax, opv.AttributeDescription, opv.Quantity, opv.DiscountAmountInclTax,
                                                opv.DiscountAmountExclTax, opv.DownloadCount, opv.LicenseDownloadId, opv.ItemWeight);
                                        //command.ExecuteNonQuery();

                                        opvId = (int)command.ExecuteScalar();
                                        if (opvId < 0)
                                            Log(String.Format("---Failed creating order product variant. The #order is {0}.", orderId));
                                        #endregion

                                        #region Create Shipment
                                        int shipmentId = -1;
                                        var shipment = new Shipment()
                                        {
                                            OrderId = orderId,
                                            Attributes = (int)AddtionalAttributes.None,
                                            Comment = String.Format("'Fake packages: # {0}'", i)
                                        };

                                        if (addr.TypeId == (int)AddressType.Residential)
                                        {
                                            //let all packages with no signature
                                            shipment.Attributes = (int)AddtionalAttributes.NoSignature;
                                        }

                                        command.CommandText = String.Format("INSERT INTO Shipment(OrderId, CreatedOnUtc, Attributes, Comment) VALUES ({0},CURRENT_TIMESTAMP,{1},{2});SELECT CAST(scope_identity() AS int)",
                                                                    shipment.OrderId, shipment.Attributes, shipment.Comment);
                                        //command.ExecuteNonQuery();

                                        shipmentId = (int)command.ExecuteScalar();
                                        if (shipmentId < 0)
                                            Log(String.Format("---Failed creating shipment. The #order is {0}.", orderId));

                                        //shipment order product variant
                                        command.CommandText =
                                                String.Format("INSERT INTO Shipment_OrderProductVariant(OrderProductVariantId, ShipmentId, Quantity) VALUES ({0},{1}, 1)",
                                               opvId, shipmentId);
                                        command.ExecuteNonQuery();

                                        var pickupLocation = new LogisticsLocation()
                                        {
                                            AddressId = userAddress.Id,
                                            Status = (int)LocationStatus.ReadyForPickUp,
                                            DistrictId = districtId
                                        };
                                        //Insert pick up location
                                        command.CommandText = String.Format("INSERT INTO LogisticsLocation(AddressId, CreatedOnUtc, Status, DistrictId) VALUES ({0},CURRENT_TIMESTAMP,{1}, {2});SELECT CAST(scope_identity() AS int)", 
                                                                        pickupLocation.AddressId, pickupLocation.Status, pickupLocation.DistrictId);
                                        //command.ExecuteNonQuery();

                                        int pickupLocationId = -1;
                                        pickupLocationId = (int)command.ExecuteScalar();
                                        if (pickupLocationId < 0)
                                            Log(String.Format("---Failed creating pick up location of shipment. The #order is {0}.", orderId));

                                        var deliveryLocation = new LogisticsLocation()
                                        {
                                            AddressId = addr.Id,
                                            Status = (int)LocationStatus.PendingDropOff,
                                            DistrictId = districtId
                                        };
                                        //Insert delivery location
                                        command.CommandText = String.Format("INSERT INTO LogisticsLocation(AddressId, CreatedOnUtc, Status, DistrictId) VALUES ({0}, CURRENT_TIMESTAMP, {1}, {2});SELECT CAST(scope_identity() AS int)", 
                                                                    deliveryLocation.AddressId, deliveryLocation.Status, deliveryLocation.DistrictId);
                                        //command.ExecuteNonQuery();

                                        int deliveryLocationId = -1;
                                        deliveryLocationId = (int)command.ExecuteScalar();
                                        if (deliveryLocationId < 0)
                                            Log(String.Format("---Failed creating delivery location of shipment. The #order is {0}.", orderId));

                                        //Insert package
                                        var package = new LogisticsPackage()
                                        {
                                            Status = (int)ShippingStatus.ReadyForPickUp,
                                            PickUpLocationId = pickupLocationId,
                                            DeliveryLocationId = deliveryLocationId,
                                            ShipmentId = shipmentId,
                                            ApplicatorId = customer.Id,
                                            DeliveryAttemptCount = 0
                                        };
                                        
                                        command.CommandText = String.Format("INSERT INTO LogisticsPackage(Status, CreatedOnUtc, PickUpLocationId, DeliveryLocationId, ShipmentId, ApplicatorId, DeliveryAttemptCount) VALUES ({0},CURRENT_TIMESTAMP, {1}, {2}, {3}, {4}, {5});SELECT CAST(scope_identity() AS int)", 
                                                                            package.Status, package.PickUpLocationId, package.DeliveryLocationId,
                                                                            package.ShipmentId, package.ApplicatorId, package.DeliveryAttemptCount);
                                        //command.ExecuteNonQuery();

                                        int packageId = -1;
                                        packageId = (int)command.ExecuteScalar();
                                        if (packageId < 0)
                                            Log(String.Format("---Failed creating package. The #order is {0}.", orderId));

                                        //Update pick up location
                                        command.CommandText = String.Format("UPDATE LogisticsLocation SET PACKAGEID = {0} WHERE Id = {1}", packageId, pickupLocationId);
                                        command.ExecuteNonQuery();

                                        //Update delivery location
                                        command.CommandText = String.Format("UPDATE LogisticsLocation SET PACKAGEID = {0} WHERE Id = {1}", packageId, deliveryLocationId);
                                        command.ExecuteNonQuery();

                                        string toZip = addr.ZipPostalCode.Trim().ToUpper().Substring(0, 3);
                                        //Find to zone
                                        command.CommandText = String.Format("SELECT * FROM LogisticsZone WHERE ZipPostalCode LIKE '%{0}%'", toZip);
                                        command.ExecuteNonQuery();

                                        string toZoneName = "";
                                        using (SqlDataReader reader = command.ExecuteReader())
                                        {
                                            // Loop over the results
                                            while (reader.Read())
                                            {
                                                toZoneName = reader["Name"].ToString().Trim();
                                                break;
                                            }
                                        }
                                        var zoneStr = String.IsNullOrEmpty(toZoneName) ? "00000" : toZoneName;

                                        shipment.TrackingNumber = String.Format("'{0}'", GenerateTrackingNumber(zoneStr, addr.ZipPostalCode, customer.Id, shipment.OrderId, packageId));

                                        //Update shipment
                                        command.CommandText = String.Format("UPDATE Shipment SET TrackingNumber = {0}, PACKAGEID = {1} WHERE Id = {2}", shipment.TrackingNumber, packageId, shipmentId);
                                        command.ExecuteNonQuery();
                                        #endregion

                                        //add surcharge
                                        var taxRate = TaxRateList.First();
                                        taxRate.Value = charge.SubTotalExclTax + charge.FuelSurchargeExclTax;
                                        var surcharge = ToSurcharges(taxRate, opv);
                                        //Insert surcharge
                                        command.CommandText = String.Format("INSERT INTO Surcharge(OrderId, OrderProductVariantId, PercentageOrFee, StateProvinceId, SurchargeTypeId, Value, Zip) VALUES ({0},{1}, {2}, {3}, {4}, {5}, {6});SELECT CAST(scope_identity() AS int)",
                                                                            orderId, surcharge.OrderProductVariantId, surcharge.PercentageOrFee, surcharge.StateProvinceId, surcharge.SurchargeTypeId, surcharge.Value, surcharge.Zip);
                                        //command.ExecuteNonQuery();
                                        int surchargeId = -1;
                                        surchargeId = (int)command.ExecuteScalar();
                                        if (surchargeId < 0)
                                            Log(String.Format("---Failed creating surcharge for order product variant. The #order is {0}.", orderId));

                                        // Commit the transaction.
                                        sqlTran.Commit();
                                        using (new Highlight(ConsoleColor.Green))
                                        {
                                            Log(String.Format("---The order have been placed successfully! The #order is {0}.", orderId));
                                        }
                                    }
                                    catch (Exception ex)
                                    {
                                        using (new Highlight(ConsoleColor.Red))
                                        {
                                            // Handle the exception if the transaction fails to commit.
                                            Log(String.Format("---Error message: {0}. For order No.{1}", ex, i));
                                        }
                                        try
                                        {
                                            // Attempt to roll back the transaction.
                                            sqlTran.Rollback();
                                        }
                                        catch (Exception exRollback)
                                        {
                                            // Throws an InvalidOperationException if the connection  
                                            // is closed or the transaction has already been rolled  
                                            // back on the server.
                                            Log(exRollback.Message);
                                        }
                                    }

                                    conn.Close();
                                };
                                #endregion

                                Console.WriteLine("---Do you want to (R)efresh or (E)xit?");
                                char k = Console.ReadKey().KeyChar;
                                if (k == 'r' || k == 'R')
                                {
                                    refresh = true;
                                }
                                else if (k == 'e' || k == 'E')
                                {
                                    return;
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                using (new Highlight(ConsoleColor.Red))
                {
                    Log("---Fatal error: {0}", ex);
                    Log("---Your IP address is {0}", GetExternalIp());
                }
            }
            finally
            {
                if (file != null)
                {
                    file.Close();
                    file = null;
                }
            }
        }

        static Surcharge ToSurcharges(TaxRate taxRate, OrderProductVariant orderProductVariant)
        {
            return new Surcharge()
            {
                OrderId = 0,
                OrderProductVariantId = orderProductVariant.Id,
                PercentageOrFee = taxRate.PercentageOrFee,
                StateProvinceId = taxRate.StateProvinceId,
                SurchargeTypeId = taxRate.SurchargeTypeId,
                Value = taxRate.Value,
                Zip = taxRate.Zip
            };
        }

        static string GetExternalIp()
        {
            string url = "http://checkip.dyndns.org/";
            WebClient webClient = new WebClient();
            Stream data = webClient.OpenRead(url);
            StreamReader reader = new StreamReader(data);
            string s = reader.ReadToEnd();
            s = s.Split(':')[1];
            s = s.Substring(1);
            string res = s.Split('<')[0];
            data.Close();
            reader.Close();

            return res;
        }

        static void Log(string format, params object[] args)
        {
            Console.WriteLine(format, args);

            if (file != null)
            {
                file.Write("{0} {1}:", DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString());
                file.WriteLine(format, args);
                file.Flush();
            }
        }

        static string ReadPassword()
        {
            string password = "";
            ConsoleKeyInfo info = Console.ReadKey(true);
            while (info.Key != ConsoleKey.Enter)
            {
                if (info.Key != ConsoleKey.Backspace)
                {
                    Console.Write("*");
                    password += info.KeyChar;
                }
                else if (info.Key == ConsoleKey.Backspace)
                {
                    if (!string.IsNullOrEmpty(password))
                    {
                        // remove one character from the list of password characters        
                        password = password.Substring(0, password.Length - 1);
                        // get the location of the cursor                    
                        int pos = Console.CursorLeft;
                        // move the cursor to the left by one character     
                        Console.SetCursorPosition(pos - 1, Console.CursorTop);
                        // replace it with space          
                        Console.Write(" ");
                        // move the cursor to the left by one character again           
                        Console.SetCursorPosition(pos - 1, Console.CursorTop);
                    }
                }
                info = Console.ReadKey(true);
            }
            // add a new line because user pressed enter at the end of their password  
            Console.WriteLine();
            return password;
        }

        static Charge CalculateTotal(decimal taxRate)
        {
            decimal fuelSurchargeRate = 0.15M;
            var charge = new Charge();
            
            charge.SubTotalExclTax = 3.99M;
            charge.FuelSurchargeExclTax = charge.SubTotalExclTax * fuelSurchargeRate;
            charge.Tax = (charge.FuelSurchargeExclTax + charge.SubTotalExclTax) * taxRate;
            return charge;
        }

        static string GenerateTrackingNumber(string zoneName, string postalCode, int applicationId, int orderId, int packageId)
        {
            // {Reserve1 2#} {Ver = 01} {Zone #2} {PostalCode = 9} {ApplicationCustomerId 4#} {OrderId 6#} {PackageId 6#}
            // {Zone 2#} {OrderId 6#} {PackageId 6#}
            StringBuilder sbTrackingNumber = new StringBuilder();

            sbTrackingNumber.Append(zoneName);
            sbTrackingNumber.Append('-');

            // Order
            sbTrackingNumber.Append((orderId % 1000000).ToString("000000"));

            // Package
            sbTrackingNumber.Append((packageId % 1000000).ToString("000000"));

            return sbTrackingNumber.ToString();
        }
    }
}
